package com.atguigu.realtime.publisher.traffic.mapper;

import com.atguigu.realtime.publisher.traffic.bean.TrafficKeyword;
import com.atguigu.realtime.publisher.traffic.bean.TrafficSession;
import com.atguigu.realtime.publisher.traffic.bean.TrafficUvCt;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;


@Mapper
public interface TrafficChannelStatsMapper {
    // 1. 获取各渠道独立访客数
    @Select(
            "select\n" +
                    " ch,sum(uv_ct) as uvCt\n" +
                    " from gmall2024_realtime.dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
                    " partition (par${date})\n" +
                    " where ch is not null \n" +
                    " group by ch\n" +
                    " order by uvCt desc;"
    )
    List<TrafficUvCt> selectUvCt(@Param("date") String date);


    // 2. 获取各渠道的 会话总数,会话平均浏览页面数 以及会话平均停留时长

    @Select(
            "select\n" +
                    "    ch,sum(sv_ct) svCt,round(sum(pv_ct)/sum(sv_ct),2) avgCt,round(sum(dur_sum)/sum(sv_ct)/3600,2) avgTime\n" +
                    " from gmall2024_realtime.dws_traffic_vc_ch_ar_is_new_page_view_window\n" +
                    " partition (par${date})\n" +
                    " where ch is not null\n" +
                    " group by ch;"
    )
    List<TrafficSession> selectSessionInfo(@Param("date") String date);

    // 3. 获取 关键词次数

    @Select(
            "select\n" +
                    "    keyword,sum(keyword_count) keywordCount\n" +
                    " from gmall2024_realtime.dws_traffic_source_keyword_page_view_window\n" +
                    " partition (par${date})\n" +
                    " group by keyword;"
    )
    List<TrafficKeyword> selectKeywordCount(@Param("date") String date);


}
